問題描述
Jika saya memiliki batasan kunci asing dari tabel itu sendiri, apakah saya perlu berhati‑hati saat menghapus seluruh tabel? (If I have a foreign key constraint of a table to itself, do I need to be careful when deleting the whole table?)
I have a SQLite table that looks like this:
CREATE TABLE myTable (
_id integer primary key autoincrement,
...
... /* some useful fields */
...
parent integer,
FOREIGN KEY (parent) REFERENCES myTable (_id)
);
Some of the entries in myTable have a parent, others don't. Business logic ensures that there are no "circles". I'm using PRAGMA foreign_keys = ON, so that foreign key violations are detected.
Question: If I want to delete the whole table, is it enough to execute DELETE FROM myTable? Or do I need to delete the "leaves" first, then their parents, etc., all the way up to avoid foreign key violations?
I've tested it and using just DELETE FROM myTable seems to work. However, I want to know whether this is just a coincidence or something I can rely upon (i.e. documented and expected behaviour).
EDIT: I don't have ON DELETE CASCADE enabled, and this is on purpose.
‑‑‑‑‑
參考解法
方法 1:
Insert
By specifying a foreign key and with PRAGMA foreign_keys = ON, starting from an empty table, SQLite will deny you any entry of data (except identities, where parent = _id) with the message:
Error: foreign key constraint failed.
So from the start, you need to make at least one entry with PRAGMA foreign_keys = OFF or a "circular" entry.
Deletion of specific entries
If you do not provide an action such as CASCADE to your foreign key, SQLite will deny deletion of a "parent entry" if the parent has children due to the foreign key constraint. This is the default behaviour, equivalent to "NO ACTION".
Deletion of all entries
Deletion of all entries is possible through DELETE FROM myTable and should not trigger the foreign key constraints on the same table, even if there is no cascade clause. I verified by test, it might not be documented.
(by Heinzi、MPelletier)